package com.khbr.employee_imfo.dao.impl;
import com.khbr.employee_imfo.dao.IEmployeeDao;
import com.khbr.employee_imfo.entity.Employee;
import com.khbr.employee_imfo.model.Condition;
import com.khbr.employee_imfo.model.PageDepart;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
/**
 * Created by John.C on 2018/11/29.
 */
public class EmployeeDao implements IEmployeeDao {
    @Override
    public void create(Employee employee) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3307/ticket_salling","root","admin");
            String sql="INSERT INTO employee_list (w_number,p_name,gender,age,job_type,entry_time) VALUES (?,?,?,?,?,?)";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,employee.getNumber());
            preparedStatement.setString(2,employee.getName());
            preparedStatement.setString(3,employee.getGender());
            preparedStatement.setInt(4,employee.getAge());
            preparedStatement.setString(5,employee.getJobType());
            preparedStatement.setDate(6,employee.getEntryTime());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void delete(int id) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3307/ticket_salling","root","admin");
            String sql="DELETE FROM employee_list WHERE id=?";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void update(Employee employee) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3307/ticket_salling","root","admin");
            String sql="UPDATE employee_list SET w_number=?,p_name=?,gender=?,age=?,job_type=?,entry_time=? WHERE id=?";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,employee.getNumber());
            preparedStatement.setString(2,employee.getName());
            preparedStatement.setString(3,employee.getGender());
            preparedStatement.setInt(4,employee.getAge());
            preparedStatement.setString(5,employee.getJobType());
            preparedStatement.setDate(6,employee.getEntryTime());
            preparedStatement.setInt(7,employee.getId());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Employee find(int id) {
        Employee employee=new Employee();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3307/ticket_salling","root","admin");
            String sql="SELECT * FROM employee_list WHERE id=?";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            ResultSet resultSet=preparedStatement.executeQuery();
            if (resultSet.next()){
                String number=resultSet.getString("w_number");
                String name=resultSet.getString("p_name");
                String gender=resultSet.getString("gender");
                int age=resultSet.getInt("age");
                String jobType=resultSet.getString("job_type");
                Date entryTime=resultSet.getDate("entry_time");
                employee.setNumber(number);
                employee.setName(name);
                employee.setGender(gender);
                employee.setAge(age);
                employee.setJobType(jobType);
                employee.setEntryTime(entryTime);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return employee;
    }

    @Override
    public List<Employee> multipleFind(PageDepart pageDepart,Condition condition) {
        List<Employee> list=new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3307/ticket_salling","root","admin");
            String sql="SELECT * FROM employee_list WHERE 1=1 ";
            if (condition.getNumber()!=null){
                sql+="AND w_number LIKE '%"+ condition.getNumber()+"%'";
            }
            if (condition.getName()!=null){
                sql+="AND p_name LIKE '%"+ condition.getName()+"%'";
            }
            sql+=" LIMIT ?,?";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1, (pageDepart.getHead()-1) * pageDepart.getCount());
            preparedStatement.setInt(2, pageDepart.getCount());
            ResultSet resultSet=preparedStatement.executeQuery();
            if (resultSet.next()){
                String number=resultSet.getString("w_number");
                String name=resultSet.getString("p_name");
                String gender=resultSet.getString("gender");
                int age=resultSet.getInt("age");
                String jobType=resultSet.getString("job_type");
                Date entryTime=resultSet.getDate("entry_time");
                Employee employee=new Employee();
                employee.setNumber(number);
                employee.setName(name);
                employee.setGender(gender);
                employee.setAge(age);
                employee.setJobType(jobType);
                employee.setEntryTime(entryTime);
                list.add(employee);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    //获取总记录数
    public int count(Condition condition){
        List<Employee> list = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/ticket_salling", "root", "admin");
            String sql = "SELECT COUNT(id) AS total FROM employee_list WHERE 1=1 ";
            if (condition.getNumber() != null) {
                sql += "  AND w_number LIKE '%" + condition.getNumber() + "%'";
            }
            if (condition.getName() != null) {
                sql += " AND p_name LIKE '%" + condition.getName() + "%'";
            }
            PreparedStatement statement = conn.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt("total");
            }
            statement.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
}